import pymysql
import database.sqllit3

'''
解析文本文件并将去存储在mysql数据库中，如果表中已经存在该行，在先删除之后再做插入
'''
# 下面两种对文件的读取方式均可：readlines()一次性读取整个文件，readline()每次读取文件的一行数据进行处理

# def parsecsv(filename):
#     '''解析文本文件,提取前面两列数据存入数据库中'''
#     date = []
#     date1 = []
#     with open(filename) as f:
#         readers = f.readlines()
#         for r in readers:
#             r = r.strip()
#             reader = r.split('\t')
#             date.append(reader[0])
#             date.append(reader[1])
#             tup = tuple(date)
#             date1.append(tup)
#             date = []
#             tup = []
#     return date1

def parsecsv(filename):
    date = []
    date1 = []
    '''解析文本文件,提取前面两列数据存入数据库中'''
    with open(filename) as f:
        while True:
            line = f.readline()
            line = line.strip()
            if line == '':
                break
            line = line.split('\t')
            date.append(line[0])
            date.append(line[1])
            tup = tuple(date)
            date1.append(tup)
            date = []
            tup = []
    return date1


def get_connect():
    '''获取mysql数据库连接'''
    # 四个参数依次是服务器地址，用户名，密码，数据库名称
    dbconn = pymysql.connect("localhost", "root", "123456", "test")
    return dbconn


def get_cursor(dbconn):
    '''该获取游标对形象'''
    if dbconn is not None:
        return dbconn.cursor()


def initdb(date):
    dbconn = get_connect()
    cu = get_cursor(dbconn)
    database.sqllit3.log('数据库连接成功')
    # mysql数据库占位符为%s
    save_sql = "INSERT INTO account values (%s, %s)"
    select_sql = "select count(*) from account where username = %s"
    del_sql = "delete from account where username = %s"
    for d in date:
        database.sqllit3.log('执行sql:[{}],参数:[{}]'.format(save_sql, d))
        # 如果表中已经存在此条数据，则先删除之后再插入
        key = d[0]
        cu.execute(select_sql, key)
        flag = cu.fetchone()  ##fetchone返回的数据为元组类型
        if flag[0] >= 1:
            cu.execute(del_sql, key)
        cu.execute(save_sql, d)
    dbconn.commit()
    dbconn.close()
    database.sqllit3.log('数据插入成功')


def main():
    filename = 'D:\个人信息\账号.txt'
    date = parsecsv(filename)
    initdb(date)


if __name__ == '__main__':
    main()